package com.h3bpm.web.common.utils;

import OThinker.Common.Data.Database.Parameter;
import OThinker.H3.Controller.ControllerBase;
import data.DataTable;

import java.lang.reflect.Field;

/**
 * @author zhengyj
 */
public class SqlUtil extends ControllerBase {



    public static String beanToString(Class javaBean,String tableName){
        String beanStr = "";
        Field[] fields = javaBean.getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            beanStr += tableName + "." + field.getName()+",";
        }
        beanStr = beanStr.substring(0,beanStr.length()-1);
        return beanStr;
    }

    public static String genInsertSql(String tableName, Object javaBean) throws Exception {
        Class bean = javaBean.getClass();
        Field[] fields = bean.getDeclaredFields();

        String tableColunStr="";
        String tableValueStr="";

        for (Field field : fields) {
            field.setAccessible(true);
            Object fieldValue = field.get(javaBean);

            //忽略时间
            if("class java.util.Date".equals(field.getGenericType().toString())){
                // tableValueStr+="to_date('"+fieldValue+"','yyyy-mm-dd hh24:mi:ss')";
                continue;
            }

            if(fieldValue != null){
                tableValueStr+="'"+fieldValue+"'"+",";
            }else {
                tableValueStr+="''"+",";
            }
            tableColunStr += field.getName()+",";
        }

        tableColunStr = tableColunStr.substring(0,tableColunStr.length()-1);
        tableValueStr = tableValueStr.substring(0,tableValueStr.length()-1);

        String sql = "INSERT INTO "+tableName + "("+tableColunStr+")"+" VALUES("+tableValueStr+")";

        return sql;
    }
//    public static Parameter[] genInsertSql(Object javaBean) throws Exception {
//        List<Parameter> parameters = new ArrayList<>();
//        Class bean = javaBean.getClass();
//        Field[] fields = bean.getDeclaredFields();
//        for (Field field : fields) {
//            field.setAccessible(true);
//            Object fieldValue = field.get(javaBean);
//            parameters.add(new Parameter(field.getName(),field.getType(),fieldValue,1));
//        }
//    }
    
    /**
     * 更新SQL拼接
     * @param tableName
     * @param javaBean
     * @return SQL语句
     */
    public static String genUpdateSql(String tableName, Object javaBean) throws Exception {
        Class bean = javaBean.getClass();
        Field[] fields = bean.getDeclaredFields();
 
        
        String tableUpdate = "";
        String tableId = "";

        for (Field field : fields) {
            field.setAccessible(true);
            Object fieldValue = field.get(javaBean);

            //忽略时间
            if("class java.util.Date".equals(field.getGenericType().toString())){
                // tableValueStr+="to_date('"+fieldValue+"','yyyy-mm-dd hh24:mi:ss')";
                continue;
            }
            String tableValueStr="";
            if(fieldValue != null){
                tableValueStr ="'"+fieldValue+"'";
            }else {
                tableValueStr ="''";
            }
            String tableColunStr = toUnderScore(field.getName());
            tableUpdate += tableColunStr + "=" + tableValueStr + ",";
             
            if("CONTRACTANDOUTSIDEUSERID".equals(tableColunStr)){
                //主键
            	tableId = ""+fieldValue;
            }
            	
        }

        tableUpdate = tableUpdate.substring(0,tableUpdate.length()-1);

        String sql = "update "+tableName + " set "+tableUpdate+""+" where contractandoutsideuserid = '"+tableId+"'";

        return sql;
    }
    /**
     * 更新SQL拼接
     * @param tableName
     * @param javaBean
     * @param
     * @return SQL语句
     */
    public static String genUpdateSql(String tableName, Object javaBean,String id) throws Exception {
    	id = id.toUpperCase();
        Class bean = javaBean.getClass();
        Field[] fields = bean.getDeclaredFields();
 
        
        String tableUpdate = "";
        String tableId = "";

        for (Field field : fields) {
            field.setAccessible(true);
            Object fieldValue = field.get(javaBean);

            //忽略时间
            if("class java.util.Date".equals(field.getGenericType().toString())){
                // tableValueStr+="to_date('"+fieldValue+"','yyyy-mm-dd hh24:mi:ss')";
                continue;
            }
            String tableValueStr="";
            if(fieldValue != null){
                tableValueStr ="'"+fieldValue+"'";
            }else {
                tableValueStr ="''";
            }
            String tableColunStr = field.getName().toUpperCase();
            tableUpdate += tableColunStr + "=" + tableValueStr + ",";
             
            if(id.equals(tableColunStr)){
                //主键
            	tableId = ""+fieldValue;
            }
            	
        }

        tableUpdate = tableUpdate.substring(0,tableUpdate.length()-1);

        String sql = "update "+tableName + " set "+tableUpdate+""+" where "+id+" = '"+tableId+"'";

        return sql;
    }
    
    public static String toUnderScore(String str){
        StringBuilder sb=new StringBuilder(str);
        //定位
        int temp=0;
        for(int i=0;i<str.length();i++){
            if(Character.isUpperCase(str.charAt(i))){
                sb.insert(i+temp, "_");
                temp+=1;
            }
        }
        return sb.toString().toUpperCase();
    }

    @Override
    public String getFunctionCode() {
        return null;
    }

    public static int executeInsert(String sql){
        int i = getEngine().getEngineConfig().getCommandFactory().CreateCommand().ExecuteNonQuery(sql);
        return i;
    }

    public static int doNoQuery(String sql){
        int i = getEngine().getEngineConfig().getCommandFactory().CreateCommand().ExecuteNonQuery(sql);
        return i;
    }

    public static int doNoQuery(String sql,Parameter[] parameters){
        int i = getEngine().getEngineConfig().getCommandFactory().CreateCommand().ExecuteNonQuery(sql,parameters);
        return i;
    }

    public static DataTable doQuery(String sql) throws Exception {
        DataTable dataTable = getEngine().getQuery().QueryTable(sql);
        return dataTable;
    }

}
